package oracle.to.mysql;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

/**
 * to data 
 *
 */
public class OracleToMysqlData {
	
	private static Logger log = Logger.getLogger("");

	public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
		String ora_url = "jdbc:oracle:thin:@orcl.hrmis.cn:3001:orcl";// oracle连接串
		String ora_user = "jytweb";// oracle用户名
		String ora_pwd = "jytweb";// oracle密码
		String my_schema = "business";// mysql实例名
		String my_user = "jytbusiness";// mysql用户名
		String my_pwd = "jytbusiness";// mysql密码
		String my_url = "jdbc:mysql://10.0.66.83:3306/" + my_schema + "?user=" + my_user + "&password=" + my_pwd
				+ "&useUnicode=true&useSSL=false&characterEncoding=UTF8";// mysql连接串
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection(ora_url, ora_user, ora_pwd);
		Connection conn2 = DriverManager.getConnection(my_url);
		Statement stmt = conn.createStatement();
		String table = "CC20";
		long time = System.currentTimeMillis();
		stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("select * from " + table + "");
		ResultSetMetaData mt = rs.getMetaData();
		int cols = mt.getColumnCount();
		String insert = "insert into " + my_schema + "." + table + " values(";
		for (int c = 1; c <= cols; c++) {
			if (c != 1) {
				insert = insert + ",";
			}
			insert = insert + "?";
		}
		insert = insert + ")";
		System.out.println(insert);
		while (rs.next()) {
			PreparedStatement stmt2 = conn2.prepareStatement(insert);
			for (int c = 1; c <= cols; c++) {
				Object o = rs.getObject(c);
				if (o != null) {
					if (o instanceof oracle.sql.TIMESTAMP) {
						oracle.sql.TIMESTAMP ts = (oracle.sql.TIMESTAMP) o;
						o = new java.sql.Timestamp(ts.timestampValue().getTime());
					} else if (o instanceof java.util.Date) {
						o = new java.sql.Timestamp(((java.util.Date) o).getTime());
					} else if (o instanceof java.sql.Clob) {
						oracle.sql.CLOB clob = (oracle.sql.CLOB) o;
						Reader red = clob.getCharacterStream();
						char[] crs = new char[10240];
						String tmp = "";
						int len;
						while ((len = red.read(crs)) != -1) {
							tmp += new String(crs, 0, len);
						}
						red.close();
						o = new String(tmp);
					}
				}
				stmt2.setObject(c, o);
			}
			log.info(stmt2.toString());
			stmt2.execute();
			stmt2.close();
		}
		log.info("用时：" + (System.currentTimeMillis() - time) + "ms");
		rs.close();
		stmt.close();
		conn.close();
		conn2.close();
	}

}